/**
 * 项目名：phms3
 * 日期：    2019-12-01 17:02:29
 * Copyright (c) 2015- HTSC-版权所有
 */

package com.joysuch.wwyt.bp.repository;

import com.joysuch.wwyt.bp.entity.BpMonitoringPointRel;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * BpMonitoringPointRelDao
 */
public interface BpMonitoringPointRelDao extends JpaRepository<BpMonitoringPointRel, String> {

    public Page<BpMonitoringPointRel> findAll(Specification<BpMonitoringPointRel> spec, Pageable pageable);

    public BpMonitoringPointRel save(BpMonitoringPointRel bean);

    public void delete(BpMonitoringPointRel bean);

    @Transactional
    public void deleteByRefTypeAndRefId(String refType, Long refId);

    public List<BpMonitoringPointRel> findByRefTypeAndRefId(String refType, Long refId);

    @Query(value = "SELECT *  FROM wwyt_bp_monitoring_point_rel where ref_type=?1 and REF_ID in (?2)",nativeQuery = true)
    public List<BpMonitoringPointRel> findByRefTypeAndRefIds(String refType, List<Long> refIds);

    public List<BpMonitoringPointRel> findByRefTypeAndRefIdAndMonitoringPointId(String refType, Long refId,
                                                                                Long monitoringPointId);

    void deleteByMonitoringPointId(Long monitoringPointId);

    public void deleteByRefTypeAndRefIdAndMonitoringPointId(String string, Long id, Long monitorPointId);

    List<BpMonitoringPointRel> findByRefTypeAndMonitoringPointId(String production_facility, Long monitorPointId);

    @Query(value = "SELECT *  FROM wwyt_bp_monitoring_point_rel WHERE ref_type = 'store_house' AND REF_ID IN (SELECT id FROM wwyt_bp_store_house WHERE CODE NOT LIKE :substring%) AND MONITORING_POINT_ID = :along", nativeQuery = true)
    List<BpMonitoringPointRel> getOtherHousePointRelList(@Param("along") Long aLong, @Param("substring") String substring);

    @Query(value = "SELECT *  FROM wwyt_bp_monitoring_point_rel WHERE ref_type = 'store_house' AND REF_ID IN(SELECT id FROM wwyt_bp_store_house WHERE parent_id = ?1) AND MONITORING_POINT_ID = ?2", nativeQuery = true)
    List<BpMonitoringPointRel> getBrotherHousePointRelList(Long parentId, Long aLong);

    /**
     * 工艺流程（manufacturing_tech）可与其他共享监测点
     * @param mhPointId
     * @param refId
     * @return
     */
    @Query(value = "SELECT *  FROM wwyt_bp_monitoring_point_rel WHERE MONITORING_POINT_ID =?1 AND ref_id!= ?2 AND ref_type!= 'manufacturing_tech'", nativeQuery = true)
    List<BpMonitoringPointRel> findByMonitoringPointIdAndRefId(Long mhPointId, Long refId);

    @Query(value = "SELECT *  FROM wwyt_bp_monitoring_point_rel WHERE MONITORING_POINT_ID =?1 AND ref_type != ?2 ", nativeQuery = true)
    List<BpMonitoringPointRel> findByMonitoringPointIdAndRefTypeNotLike(Long mhPointId, String store_house);

    List<BpMonitoringPointRel> findByMonitoringPointIdAndRefType(Long mhPointId, String store_house);

    BpMonitoringPointRel findFirstByMonitoringPointId(Long monitoringPointId);

    @Query(value = "SELECT DISTINCT(MONITORING_POINT_ID) FROM wwyt_bp_monitoring_point_rel WHERE ref_type != ?1", nativeQuery = true)
    List<Long> getPointIdListAndRefTypeNotEq(String ref);

    @Query(value = "SELECT * FROM wwyt_bp_monitoring_point_rel WHERE MONITORING_POINT_ID =?1 AND ref_type != ?2 ORDER BY ID DESC  LIMIT 1", nativeQuery = true)
    BpMonitoringPointRel findByPointIdAndRefTypeNotEq(Long id, String manufacturing_tech);
}
